#! /bin/bash
HIVE_HOME=/usr/bin/hive
Last_DATE=$(date -d "-1 day" +%Y-%m-%d)
${HIVE_HOME} -S -e "
SET hive.auto.convert.join=false;
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions.pernode=10000;
SET hive.exec.max.dynamic.partitions=10000;
SET hive.exec.max.created.files=150000;
INSERT INTO zz15_dws_renxinlei.class_attendance PARTITION(yearinfo,monthinfo,dayinfo)
SELECT 
    dateinfo,
    class_id,
    count(
        CASE
            WHEN sa.morning_att IN('0','1') THEN sa.student_id
            ELSE NULL
        END
    ) AS morning_att_count,
    count(
        CASE
            WHEN sa.afternoon_att IN('0','1') THEN sa.student_id
            ELSE NULL
        END
    ) AS afternoon_att_count,
    count(
        CASE
            WHEN sa.evening_att IN('0','1') THEN sa.student_id
            ELSE NULL
        END
    ) AS evening_att_count,
    count(
        CASE
            WHEN sa.morning_att ='1' THEN sa.student_id
            ELSE NULL
        END
    ) AS morning_late_count,
    count(
        CASE
            WHEN sa.afternoon_att ='1' THEN sa.student_id
            ELSE NULL
        END
    ) AS afternoon_late_count,
    count(
        CASE
            WHEN sa.evening_att ='1' THEN sa.student_id
            ELSE NULL
        END
    ) AS evening_late_count,
    yearinfo,
    monthinfo,
    dayinfo
FROM zz15_dws_renxinlei.student_attendance sa
WHERE Last_DATE=${Last_DATE}
GROUP BY dateinfo,yearinfo,monthinfo,dayinfo,class_id;